package com.turing.post.salesmanage.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.salesmanage.util.DepositOrder;
import com.turing.post.salesmanage.util.DepositOrderDetail;
import com.turing.post.salesmanage.util.DepositOrderDetailDao;
import com.turing.post.salesmanage.util.PersonalCustom;
import com.turing.post.salesmanage.util.PersonalCustomDao;
import com.turing.post.salesmanage.util.SaleOrder;
import com.turing.post.salesmanage.util.SaleOrderCardDetail;
import com.turing.post.salesmanage.util.SaleOrderCardDetailDao;
import com.turing.post.salesmanage.util.SaleOrderRealNameDetailDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * 充值单卡充值明细业务接口实现（DepositOrderDetail）
 * 
 * @author zxp
 * @Jan 17, 2013
 */
@Stateless(mappedName = "salesmanage.impl.DepositOrderDetailDaoImpl")
@Remote
@Local
public class DepositOrderDetailDaoImpl implements DepositOrderDetailDao {

	@EJB(mappedName = "salesmanage.impl.SaleOrderCardDetailDaoImpl")
	private SaleOrderCardDetailDao saleOrderCardDetailDaoImpl;

	@EJB(mappedName = "salesmanage.impl.PersonalCustomDaoImpl")
	private PersonalCustomDao personalCustomDaoImpl;

	@EJB(mappedName = "salesmanage.impl.SaleOrderRealNameDetailDaoImpl")
	private SaleOrderRealNameDetailDao saleOrderRealNameDetailDaoImpl;

	@PersistenceContext
	protected EntityManager _em;

	@Override
	public DepositOrderDetail getDepositOrderDetailByPK(DepositOrderDetail dod) {
		return _em.find(DepositOrderDetail.class, dod);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositOrderDetail> getDepositOrderDetailListByDepositOrderId(
			BigInteger depositOrderId) {
		String sql = "select * from DepositOrderDetail t where t.DepositOrderId="
				+ depositOrderId + " order by batchNo";
		Query query = _em.createNativeQuery(sql, DepositOrderDetail.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DepositOrderDetail> getDepositOrderDetailListByCondition(
			BigInteger depositOrderId, Integer page) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sql = "select * from DepositOrderDetail where depositorderid ="
				+ depositOrderId + " order by batchNo,cardSeqNo";
		Query query = _em.createNativeQuery(sql, DepositOrderDetail.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		return query.getResultList();
	}

	@Override
	public BigInteger getDepositOrderDetailTotalByCondition(
			BigInteger depositOrderId) {
		String sql = "select nvl(count(*),0) from DepositOrderDetail where depositorderid ="
				+ depositOrderId;
		Query query = _em.createNativeQuery(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	/**
	 * 根据购卡人类型名称查询批量充值卡列表
	 * 
	 * @param webInput
	 * @param serchType
	 *            0 - 充值单 1 - 销售单 未充值过数据选择 查询充值单已充值列表还是销售单已购卡列表
	 * @param buyerType
	 *            购卡人类型
	 * @param name
	 *            名称
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<DepositOrderDetail> getDepositOrderCardList(
			PCPSWebInput webInput, String serchType, String buyerType,
			String name) {
		if (webInput == null || PCPSUtil.isEmpty(serchType)
				|| PCPSUtil.isEmpty(buyerType) || PCPSUtil.isEmpty(name)
				|| PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		String selCond = "";
		// 购卡人类型
		if (buyerType.equals(SaleOrder.SaleOrder_BuyerType_Person)) {
			selCond = "PersonalBuyerId in (select p.personalid from PersonalCustom p where p.name like '%"
					+ name + "%')";
		} else if (buyerType.equals(SaleOrder.SaleOrder_BuyerType_company)) {
			selCond = "OrganizeBuyerId in (select o.organizeid from OrganizeCustom o where o.fullname like '%"
					+ name + "%')";
		}
		String sql = "";
		// 返回列表
		List<DepositOrderDetail> returnList = new ArrayList<DepositOrderDetail>();
		// 客户信息
		HashMap<BigInteger, PersonalCustom> mappersonal = new HashMap<BigInteger, PersonalCustom>();
		PersonalCustom pcobj = null;
		// 查询充值单中卡列表
		if ("0".equals(serchType)) {
			sql = "select distinct(cardseqno),batchno,cardno,CustomId from DepositOrderDetail where cardseqno in (select distinct(d.cardseqno) cardseqno from DepositOrderDetail d where depositorderid in(select d.depositorderid from DepositOrder d where salebranchno='"
					+ webInput.getOperBranchNo()
					+ "' and "
					+ selCond
					+ ")) order by batchno,cardseqno";
			Query query = _em.createNativeQuery(sql);
			List<Object[]> result = query.getResultList();
			Object[] objs = null;
			DepositOrderDetail dod = null;
			for (int i = 0; i < result.size(); i++) {
				objs = result.get(i);
				dod = new DepositOrderDetail();
				if (objs[3] != null) {
					if (mappersonal.get(objs[3]) == null) {
						pcobj = personalCustomDaoImpl
								.getPersonalCustomByPersonalId(new BigInteger(
										objs[3].toString()));
						mappersonal.put(pcobj.getPersonalId(), pcobj);
						dod.setName(pcobj.getName());
						dod.setCustomIdNo(pcobj.getIdNo());
					} else {
						dod.setName(mappersonal.get(dod.getCustomId())
								.getName());
						dod.setCustomIdNo(mappersonal.get(dod.getCustomId())
								.getIdNo());
					}
				}
				dod.setBatchNo(objs[1].toString());
				dod.setCardSeqNo(objs[0].toString());
				dod.setCardNo(objs[2].toString());
				returnList.add(dod);
			}
			return returnList;
		}
		// 查询销售单中卡列表
		if ("1".equals(serchType)) {
			sql = "select saleorderid from saleorder where saletime=("
					+ "select max(s.saletime) from Saleorder s where salebranchno='"
					+ webInput.getOperBranchNo() + "' and " + selCond + ")";
			Query query = _em.createNativeQuery(sql);
			List<Object> result = query.getResultList();
			// 销售单列表
			BigInteger saleOrderId = BigInteger.ZERO;
			DepositOrderDetail dod = null;
			List<String> acList = null;
			String cardSeqNo = "";
			String batchNo = "";

			BigInteger customId = null;

			if (result == null && result.size() < 1) {
				return null;
			}

			HashMap<String, BigInteger> map = null;
			saleOrderId = new BigInteger(result.get(0).toString());

			List<SaleOrderCardDetail> saleOrderCardDetailList = saleOrderCardDetailDaoImpl
					.getSaleOrderCardDetailListBySaleOrderId(saleOrderId);

			map = saleOrderRealNameDetailDaoImpl
					.getSaleOrderRealNameDetailList(saleOrderId);
			for (int j = 0; j < saleOrderCardDetailList.size(); j++) {
				// 卡序号列表
				acList = getCardSeqNoList(saleOrderCardDetailList.get(j));
				batchNo = saleOrderCardDetailList.get(j).getBatchNo();
				for (int k = 0; k < acList.size(); k++) {
					cardSeqNo = acList.get(k);
					dod = new DepositOrderDetail();
					dod.setBatchNo(batchNo);
					dod.setCardSeqNo(cardSeqNo);
					if (map != null) {
						customId = map.get(cardSeqNo);
						if (customId != null) {
							pcobj = personalCustomDaoImpl
									.getPersonalCustomByPersonalId(customId);
							if (mappersonal.get(pcobj.getPersonalId()) == null) {
								mappersonal.put(pcobj.getPersonalId(), pcobj);
								dod.setName(pcobj.getName());
								dod.setCustomIdNo(pcobj.getIdNo());
							} else {
								dod
										.setName(mappersonal.get(customId)
												.getName());
								dod.setCustomIdNo(mappersonal.get(customId)
										.getIdNo());
							}
						}
					}
					returnList.add(dod);
				}
			}
			return returnList;
		}
		return null;
	}

	private List<String> getCardSeqNoList(SaleOrderCardDetail socd) {
		List<String> str = new ArrayList<String>();
		if (socd.getStartCardSeqNo().trim().length() < 18) {
			Integer s = Integer.parseInt(socd.getStartCardSeqNo().substring(6,
					12));
			Integer e = Integer.parseInt(socd.getEndCardSeqNo()
					.substring(6, 12));
			for (int i = s; i <= e; i++) {
				str.add(socd.getBatchNo() + String.format("%06d", i));
			}
			return str;
		} else {
			Integer s = Integer.parseInt(socd.getStartCardSeqNo().substring(12,
					18));
			Integer e = Integer.parseInt(socd.getEndCardSeqNo().substring(12,
					18));
			for (int i = s; i <= e; i++) {
				str.add(socd.getBatchNo() + String.format("%06d", i));
			}
			return str;
		}
	}

	@Override
	public void insertDepositOrderDetail(DepositOrderDetail dod)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, dod);
	}

	@Override
	public Integer deleteDepositOrderDetailByDepositOrderId(
			BigInteger depositOrderId) throws PCPSApplicationException {
		String sql = "delete from DepositOrderDetail where DepositOrderId=?1";
		Query query = _em.createNativeQuery(sql)
				.setParameter(1, depositOrderId);
		try {
			return query.executeUpdate();
		} catch (Exception e) {
			return -1;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getDepositOrderCardByAlarmRule4(
			String issueCompanyNo, String startTime, String endTime) {
		if (PCPSUtil.isEmpty(issueCompanyNo) || PCPSUtil.isEmpty(startTime)
				|| PCPSUtil.isEmpty(endTime))
			return new ArrayList<Object[]>();

		String sql = " select a.depositorderid,a.customid,sum(a.depositamount),c.name,b.salebranchno,to_char(b.trandate,'yyyy-mm-dd') "
				+ " from DepositOrderDetail a,DepositOrder b,PersonalCustom c "
				+ " where a.depositorderid = b.depositorderid "
				+ " and a.customid = c.personalid  "
				+ " and substr(b.salebranchno,0,6) = ?1 "
				+ " and b.trandate >= to_date(?2,'yyyy-mm-dd') "
				+ " and b.trandate <= to_date(?3,'yyyy-mm-dd') "
				+ " and b.orderstatus <> ?4 "
				+ " group by a.depositorderid,a.customid,c.name,b.salebranchno,b.trandate "
				+ " order by a.depositorderid asc ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, startTime);
		query.setParameter(3, endTime);
		query.setParameter(4, DepositOrder.DEPORDER_ORDERSTATUS_CANCEL);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getDepositOrderCardDetailReportByOrderId(
			BigInteger orderId) {
		String sql = "select t.cardno,c.cardclassicname,"
				+ "case when m.mediatype= '0' then '磁卡' when m.mediatype= '1' then 'CPU多界面卡' end as mediatype,"
				+ "t.depositamount,t.cardseqno"
				+ " from depositorderdetail t,makecardcontrol m,cardclassic c,activatecard a"
				+ " where a.cardclassicid=c.cardclassicid"
				+ " and t.cardseqno = a.cardseqno and t.batchno=m.batchno"
				+ " and t.depositorderid=?1 order by t.cardseqno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, orderId);
		return query.getResultList();
	}
}